package cn.crism.blog.repository;

import cn.crism.blog.entity.Article;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface ArticleRepository extends JpaRepository<Article,Integer> {

    Page<Article> findAllByArticleStatusAndArticleTitleLike(Boolean articleStatus, String articleTitle, Pageable pageable);
    Page<Article> findAllByArticleStatusOrderByUpdateTimeDesc(Boolean articleStatus, Pageable pageable);
    Page<Article> findAllByArticleStatusAndArticleTagsLikeOrderByUpdateTimeDesc(Boolean articleStatus,String articleTags, Pageable pageable);
    /**
     * 文章归档信息获取
     * @return
     */
    @Query(value="select year(update_time) as year,month(update_time) as month,"
            + "count(*) as count from article group by year(update_time) DESC,month(update_time) DESC limit ?1,?2",
            countQuery="select count(1) from (select count(1) from article group by year(update_time),month(update_time))",nativeQuery=true)
    List<Object[]> findArticleGroupByTime(int startRow, int pageSize);

    @Query(value = "SELECT count(*) FROM( SELECT count(*) FROM article GROUP BY YEAR(update_time) , MONTH(update_time)) AS a",nativeQuery = true)
    Integer countArticleByTime();

    /**
     * 按月份获取文章信息
     * @return
     */
    @Query("from Article where date_format(update_time,'%Y%m')=date_format((:yearmonth),'%Y%m') order by update_time desc")
    List<Article> findArticleByMonth(@Param("yearmonth")Date yearmonth);
}
